{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ExcelToSqlite : 初始化数据库实例\n",
      "ExcelToSqlite : Excel文件 转 db\n",
      "ExcelToSqlite : 创建表 分公司内勤花名册\n",
      "ExcelToSqlite : 创建表 分公司外勤花名册\n",
      "ExcelToSqlite : 创建表 总公司花名册\n"
     ]
    }
   ],
   "source": [
    "#!/usr/bin/env python\n",
    "#-*-coding:utf-8-*- \n",
    "\n",
    "# reference: https://blog.csdn.net/jinmie0193/article/details/96130678\n",
    "\n",
    "import sqlite3\n",
    "import xlrd\n",
    "\n",
    "    \n",
    "class ExcelToSqlite(object):\n",
    "    debug_mode = 0\n",
    "    exe = \"     执行: \"\n",
    "    output = \"     输出: \"\n",
    "    sheetDataStartIndex = 1  # 数据开始计算的行数，如第0行是表头，第1行及之后是数据\n",
    "\n",
    "    \n",
    "    def __init__(self, dbName):\n",
    "        print(\"ExcelToSqlite : \"+\"初始化数据库实例\")\n",
    "        super(ExcelToSqlite, self).__init__()\n",
    "        self.conn = sqlite3.connect(dbName)\n",
    "        self.cursor = self.conn.cursor()\n",
    " \n",
    "    def __del__(self):\n",
    "        print(\"ExcelToSqlite : \"+\"释放数据库实例\")\n",
    "        self.cursor.close()\n",
    "        self.conn.close()\n",
    " \n",
    "    def ExcelToDb(self, excelName, sheetIndex, tableName):\n",
    "        \"\"\"\n",
    "        excel转化为sqlite数据库表\n",
    "        :param excelName:excel名\n",
    "        :param sheetIndex:excel中sheet位置\n",
    "        :param tableName:数据库表名\n",
    "        \"\"\"\n",
    "        print(\"ExcelToSqlite : \"+\"Excel文件 转 db\")\n",
    "        self.tableName = tableName\n",
    "        excel = xlrd.open_workbook(excelName)\n",
    "        sheet_names = excel.sheet_names()\n",
    "        for sheet_name in sheet_names:\n",
    "            \n",
    "            sheet = excel.sheets()[sheetIndex]  # sheets 索引\n",
    "            self.sheetRows = sheet.nrows  # excel 行数\n",
    "            self.sheetCols = sheet.ncols  # excle 列数\n",
    "            fieldNames = sheet.row_values(0)  # 得到表头字段名\n",
    "\n",
    "    #         print(self.sheetRows, self.sheetCols)\n",
    "    #         print(fieldNames)\n",
    "\n",
    "            # 创建表\n",
    "            fieldTypes = \"\"\n",
    "            for index in range(fieldNames.__len__()):\n",
    "                if (index != fieldNames.__len__() - 1):\n",
    "                    fieldTypes += fieldNames[index] + \" text,\"\n",
    "                else:\n",
    "                    fieldTypes += fieldNames[index] + \" text\"\n",
    "            self.__CreateTable(sheet_name, fieldTypes)\n",
    "\n",
    "            # 插入数据\n",
    "            for rowId in range(self.sheetDataStartIndex, self.sheetRows):\n",
    "                fieldValues = sheet.row_values(rowId)\n",
    "                self.__Insert(sheet_name, fieldNames, fieldValues)\n",
    " \n",
    "    def __CreateTable(self, tableName, field):\n",
    "        \"\"\"\n",
    "        创建表\n",
    "        :param tableName: 表名\n",
    "        :param field: 字段名及类型\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        print(\"ExcelToSqlite : \"+\"创建表 \" + tableName)\n",
    "        sql = 'create table if not exists %s(%s)' % (tableName, field)  # primary key not null\n",
    "        if self.debug_mode == 1:\n",
    "            print(\"ExcelToSqlite : \"+self.exe + sql)\n",
    "        self.cursor.execute(sql)\n",
    "        self.conn.commit()\n",
    " \n",
    "    def __Insert(self, tableName, fieldNames, fieldValues):\n",
    "        \"\"\"\n",
    "        插入数据\n",
    "        :param fieldNames: 字段list\n",
    "        :param fieldValues: 值list\n",
    "        \"\"\"\n",
    "        # 通过fieldNames解析出字段名\n",
    "        names = \"\"  # 字段名，用于插入数据\n",
    "        nameTypes = \"\"  # 字段名及字段类型，用于创建表\n",
    "        for index in range(fieldNames.__len__()):\n",
    "            if (index != fieldNames.__len__() - 1):\n",
    "                names += fieldNames[index] + \",\"\n",
    "                nameTypes += fieldNames[index] + \" text,\"\n",
    "            else:\n",
    "                names += fieldNames[index]\n",
    "                nameTypes += fieldNames[index] + \" text\"\n",
    "                \n",
    "        # 通过fieldValues解析出字段对应的值\n",
    "        values = \"\"\n",
    "        for index in range(fieldValues.__len__()):\n",
    "            cell_value = str((fieldValues[index]))\n",
    "            if (isinstance(fieldValues[index], float)):\n",
    "                cell_value = str((int)(fieldValues[index]))  # 读取的excel数据会自动变为浮点型，这里转化为文本\n",
    "            if (index != fieldValues.__len__() - 1):\n",
    "                values += \"\\'\" + cell_value + \"\\',\"\n",
    "            else:\n",
    "                values += \"\\'\" + cell_value + \"\\'\"\n",
    "        # 将fieldValues解析出的值插入数据库\n",
    "        sql = 'insert into %s(%s) values(%s)' % (tableName, names, values)\n",
    "        if self.debug_mode == 1:         \n",
    "            print(\"ExcelToSqlite : \"+self.exe + sql)\n",
    "        self.cursor.execute(sql)\n",
    "        self.conn.commit()\n",
    " \n",
    "    def Query(self, tableName):\n",
    "        \"\"\"\n",
    "        查询数据库表中的数据\n",
    "        :param tableName:表名\n",
    "        \"\"\"\n",
    "        print(\"ExcelToSqlite : \"+\"查询表 \" + tableName)\n",
    "        sql = 'select * from %s' % (tableName)\n",
    "        if self.debug_mode == 1:\n",
    "            print(\"ExcelToSqlite : \"+self.exe + sql)\n",
    "        self.cursor.execute(sql)\n",
    "        results = self.cursor.fetchall()  # 获取所有记录列表\n",
    "        index = 0\n",
    "        for row in results:\n",
    "            print(\"ExcelToSqlite : \"+self.output + \"index=\" + index.__str__() + \" detail=\" + str(row))  # 打印结果\n",
    "            index += 1\n",
    "        print(\"ExcelToSqlite : \"+self.output + \"共计\" + results.__len__().__str__() + \"条数据\")\n",
    " \n",
    "    def executeSqlCommand(self, sqlCommand):\n",
    "        \"\"\"\n",
    "        执行输入的sql命令\n",
    "        :param sqlCommand: sql命令\n",
    "        \"\"\"\n",
    "#         print(\"ExcelToSqlite : \"+\"执行自定义sql \" + tableName)\n",
    "\n",
    "        if self.debug_mode == 1:\n",
    "            print(\"ExcelToSqlite : \"+self.exe + sqlCommand)\n",
    "        self.cursor.execute(sqlCommand)\n",
    "        results = self.cursor.fetchall()\n",
    "        \n",
    "        if self.debug_mode == 1:\n",
    "            print(\"ExcelToSqlite : \"+self.output + str(results))\n",
    "            \n",
    "        for index in range(0, results.__len__()):\n",
    "            print(\"ExcelToSqlite : \"+self.output + str(results[index]))\n",
    "        self.conn.commit()\n",
    " \n",
    "    def getTableNames(self):\n",
    "        self.cursor.execute(\"select name from sqlite_master where type='table' order by name\")\n",
    "        results = self.cursor.fetchall()\n",
    "        return results\n",
    "    \n",
    "dbName = \"cmrh_hr_db\"  # 数据库名,数据库不存在会自动创建，路径不存在会执行失败\n",
    "excelName = \"01.招商仁和人寿_总分公司员工信息清单_20210524_陆范成.xls\"  # excel名(可加路径)\n",
    " \n",
    "\n",
    "es = ExcelToSqlite(dbName)\n",
    "es.ExcelToDb(excelName, 0, '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('分公司内勤花名册',)\n",
      "('分公司外勤花名册',)\n",
      "('总公司花名册',)\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# es.executeSqlCommand(\"select 员工ID,员工姓名 from \" + tableName + \" where 员工ID = 10041169\" )\n",
    "tables =  es.getTableNames() #tpye tuple\n",
    "\n",
    "for t in tables:\n",
    "    print(t)\n",
    "print(type(t[0]))\n",
    "# es.Query(t)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
